# -*- coding: utf-8 -*-
import gitee_package
import log_package
import ini_package
from playwright_package import os_package
from gitee_package import re
from playwright_package import time_package
import playwright_package
import excel_package

# 数据清洗
def clean_data(file_path):
    """
    清洗数据
    :param ex:excel对象
    :return:
    """
    ex = excel_package.Excel_Option(file_path)
    ex.open_file()
    try:
        for sheet in ex.list_sheets():
            '''删除所有格式'''
            ex.clear_region_format(sheet)

            '''文本居中'''
            ex.modify_region_alignment(sheet, horizontal="center", vertical="center")

            '''删除None列'''
            while True:
                k = ex.get_sheet_headers(sheet)
                # print(k)
                if None in k:

                    # print(sheet)
                    ex.delete_column(sheet, k.index(None) + 1)
                else:
                    break

            '''删除None行'''
            # 配置行
            k = ex.get_column_values(sheet, "A")
            # 如果第一列数据是有行是None就要删除这一行
            while True:
                k = ex.get_column_values(sheet, "A")
                if None in k:
                    # print(k)
                    ex.delete_row(sheet, k.index(None) + 1)
                    # print(sheet)
                else:
                    break

            '''重置索引'''
            rows = ex.get_sheet_max_rows(sheet)
            p = 1
            for i in range(2, rows + 1):
                ex.modify_cell_value(sheet, i, 1, p)
                p += 1
        ex.save_file()
        ex.close_file()
        return True
    except Exception as e:
        ex.close_file()
        return False


# 分割学校名称
def extract_school_name(item):
    if "竞赛" in item:
        match = re.search(r'(\w+)竞赛', item)
        if match:
            return match.group(1)
        else:
            return None

    elif "普" in item:
        match = re.search(r'(\w+)普', item)
        if match:
            return match.group(1)
        else:
            return None
    else:
        return None


# 分割年级
def extract_text_by_nodes(text):
    patterns = [r'(.*?)(普|竞赛)(.*)', r'(.*)(普|竞赛)$']
    for pattern in patterns:
        match = re.search(pattern, text)
        if match:
            return match.group(1), match.group(2), match.group(3)
    return "", "", ""


main_file_name = 'file/2025春季名单.xlsx'
creat_new_file_name = 'file/金牛区学校信息表.xlsx'

ex_main = excel_package.Excel_Option(main_file_name)
ex_main.open_file()

ex_cn = excel_package.Excel_Option(creat_new_file_name)
ex_cn.create_new_excel()

sheet_names_main = ex_main.list_sheets()
if '竞赛模板' in sheet_names_main:
    sheet_names_main.remove('竞赛模板')
if '普模板' in sheet_names_main:
    sheet_names_main.remove('普模板')

school_names = []
for sheet in sheet_names_main:
    # print(sheet)
    school_names.append(extract_school_name(sheet))

school_names = list(set(school_names))
print(school_names)
for sc in school_names:
    all_rs = 0
    pj_rs = 0
    js_rs = 0
    for sheet in sheet_names_main:
        if extract_school_name(sheet) == sc:
            all_rs +=ex_main.get_sheet_max_rows(sheet)
            if extract_text_by_nodes(sheet)[1] == '普':
                print(ex_main.get_sheet_headers(sheet))
                print(ex_main.get_sheet_all_data(sheet))
                print(sheet)
                for k,v in ex_main.get_sheet_all_data(sheet):
                    if v['账号'] is not None:
                        pj_rs += 1
            else:
                print(ex_main.get_sheet_headers(sheet))
                print(ex_main.get_sheet_all_data(sheet))
                js_rs += ex_main.get_sheet_max_rows(sheet)
                # for k,v in ex_main.get_sheet_all_data(sheet):
                #     if v['账号'] is not None:
                #         pj_rs += 1
